Course: Microsoft Power BI Training
Topic: Connecting to Excel and CSV Files
Course Link: Microsoft Power BI Training at JustAcademy
Demo Link: Register for a Course Demo
JustAcademy Website: Visit JustAcademy
Microsoft Power BI is a business intelligence and data visualization platform used to transform raw data into meaningful reports, dashboards, and analytical insights. Before creating visualizations, users must connect Power BI to a data source that contains the required information.
Excel workbooks and CSV files are two of the most commonly used data sources in Power BI. Organizations frequently use Excel to manage sales, expenses, inventory, customer details, employee information, and financial records. CSV files are often used to export data from business applications, databases, websites, and other information systems.
Power BI Desktop provides connectors that allow users to import these files, inspect their contents, clean the data using Power Query, and prepare it for reporting.
A retail company maintains its sales transactions in an Excel workbook and receives monthly inventory exports in CSV format. The management team wants to analyze sales by product, compare regional performance, and understand inventory levels.
Power BI can connect to both files, prepare the information, and combine the relevant tables into a model for reporting. This allows the company to analyze different business activities in one reporting environment.
After completing this topic, learners should be able to:
Microsoft Excel is a spreadsheet application used to store, organize, calculate, and analyze information. Excel files can contain worksheets, tables, formulas, named ranges, and other supported workbook structures.
| Extension | Description | Typical Use |
|---|---|---|
| .xlsx | Standard Excel workbook format | Business data and reports |
| .xlsm | Excel workbook that can contain macros | Workbooks using VBA macros |
| .xls | Legacy Excel workbook format | Older spreadsheet systems |
| .xlsb | Excel binary workbook format | Workbooks stored in binary format |
Support for specific formats depends on the connector, environment, and required components. Modern .xlsx files are generally a convenient choice for Power BI data preparation.
When preparing data for Power BI, converting a simple data range into an Excel Table often makes the structure easier to identify and maintain.
Benefits include:
Example: Select the sales data in Excel, choose Insert > Table, confirm that the table has headers, and assign the table a meaningful name such as SalesData.
Proper preparation reduces errors during data import and helps create a reliable Power BI model.
Each column should have a meaningful name that describes its contents.
Good examples: OrderID, OrderDate, ProductName, Region, Quantity, SalesAmount.
Avoid: Column1, Column2, Field3, or multiple columns with confusing or repeated headings.
Each column should generally contain one kind of information. For example, a SalesAmount column should contain numeric values rather than a mixture of numbers, text labels, and comments.
Merged cells can make a table difficult to interpret because one value may appear to represent several rows or columns. Use a simple rectangular data structure instead.
Blank rows and columns may interfere with data detection or create unnecessary records. Remove them when they do not have a legitimate purpose.
Dates should be recognizable as dates, and numeric fields should contain valid numeric values. Regional formats can affect interpretation. For example, 04/05/2026 can mean April 5 or May 4 depending on the date convention.
| OrderID | OrderDate | Product | Region | Quantity | SalesAmount |
|---|---|---|---|---|---|
| 1001 | 2026-01-05 | Laptop | West | 2 | 120000 |
| 1002 | 2026-01-12 | Monitor | North | 3 | 45000 |
| 1003 | 2026-02-03 | Keyboard | South | 5 | 10000 |
| 1004 | 2026-02-14 | Laptop | East | 1 | 60000 |
This structure is suitable for basic sales analysis because each row represents an order record and each column represents a particular attribute.
Power BI Desktop provides an Excel workbook connector that lets users browse for an Excel file and select the relevant data objects.
Navigator displays the data objects that Power BI can identify in the selected workbook. Depending on the workbook, these objects may include worksheets, Excel Tables, and named ranges.
Navigator helps users:
A worksheet may contain multiple data ranges, notes, totals, and formatting. An Excel Table provides a clearly defined rectangular data structure.
| Feature | Worksheet | Excel Table |
|---|---|---|
| Structure | May contain multiple ranges and other content | Defined rows and columns |
| Column names | May require header promotion or cleanup | Typically has explicit column headers |
| Data selection | May include unnecessary worksheet content | Usually identifies the intended data range clearly |
| Recommended use | When the worksheet itself is the intended source | When importing a structured data table |
Best practice: Prefer a well-structured Excel Table when the data represents a single logical dataset.
After selecting data in Navigator, Power BI generally provides options to load the data directly or transform it before loading.
The Load option imports the selected data using the current query configuration. It is suitable when the source is already clean and correctly structured.
Use Load when:
Transform Data opens Power Query Editor, where users can inspect and prepare the data before loading it into the model.
Use Transform Data when:
| Option | Main Purpose | Result |
|---|---|---|
| Load | Import selected data directly | Data becomes available in the Power BI model |
| Transform Data | Prepare and clean the data | Power Query Editor opens for additional processing |
CSV stands for Comma-Separated Values. It is a text-based file format used to store tabular information. A CSV file commonly stores one record per line, with fields separated by commas.
CSV files are frequently created by exporting data from databases, websites, CRM systems, accounting tools, inventory applications, and other business systems.
OrderID,OrderDate,Product,Region,Quantity,SalesAmount
1001,2026-01-05,Laptop,West,2,120000
1002,2026-01-12,Monitor,North,3,45000
1003,2026-02-03,Keyboard,South,5,10000
1004,2026-02-14,Laptop,East,1,60000The first line contains column headings, and the following lines contain the data records.
| Feature | Excel Workbook | CSV File |
|---|---|---|
| Typical extension | .xlsx | .csv |
| Multiple worksheets | Supported | Not supported within a single CSV file |
| Cell formatting | Supported | Not stored as spreadsheet formatting |
| Formulas | Can be stored in workbook cells | Stores text values rather than workbook formulas |
| Data structure | May contain tables, ranges, and multiple worksheets | Typically a single table-like structure |
| Typical purpose | Spreadsheet work and structured data storage | Data exchange and system exports |
Although CSV files are simple, differences in delimiters, encoding, and regional formats can cause import problems.
A delimiter separates one field from another. A comma is common, but some files use semicolons, tabs, or other separators.
For example, comma-separated data looks like this:
Product,Region,SalesAmount
Laptop,West,120000
Monitor,North,45000A semicolon-separated file may look like this:
Product;Region;SalesAmount
Laptop;West;120000
Monitor;North;45000If Power BI selects the wrong delimiter, several fields may appear in a single column.
The first row often contains the column names. Confirm that Power BI correctly identifies the headers. If the file has no header row, do not promote the first data record into column names.
Text encoding determines how characters are represented in the file. An incorrect encoding can cause accented characters, currency symbols, and other non-English characters to display incorrectly.
CSV files may place text inside quotation marks, especially when a field contains a comma.
CustomerID,CustomerName,City
1,"Shah, Amit",Mumbai
2,"Patel, Neha",PuneA suitable CSV parser can interpret the comma inside a quoted name as part of the field rather than as a separator between columns.
Review date formats, decimal separators, and thousands separators before loading the file. Power Query allows users to select a suitable locale when interpreting certain data types.
The preview helps you inspect how Power BI interprets the file before loading it.
Review these items:
When the CSV data is loaded, Power BI creates a table in the semantic model. You can then use the fields in report visuals, create measures, define relationships, and perform further analysis.
A delimiter is the character or symbol used to separate fields in a text file. Choosing the correct delimiter is essential for accurate column detection.
| Delimiter | Name | Example |
|---|---|---|
| , | Comma | Product,Region,Sales |
| ; | Semicolon | Product;Region;Sales |
| Tab | Tab-separated | Product [Tab] Region [Tab] Sales |
| | | Pipe | Product|Region|Sales |
Suppose a file contains:
Product;Region;SalesAmount
Laptop;West;120000
Monitor;North;45000If Power BI treats the comma as the delimiter, the entire row may remain in one column because the file uses semicolons.
Solution: Choose the correct delimiter in the preview or adjust the source file to use a consistent format.
Column headers help users understand the meaning of the data. Power BI may detect headers automatically, but the result should always be checked.
If the first row contains column names but Power Query treats it as ordinary data, use the Use First Row as Headers option in Power Query Editor.
Use clear, descriptive names that are easy to understand in reports and calculations.
Examples:
If the CSV contains only data rows, do not promote the first row as headers. Instead, assign appropriate column names after import.
Data types define how Power BI interprets values. Correct data types are important for calculations, sorting, filtering, relationships, and visualizations.
| Data Type | Example | Typical Use |
|---|---|---|
| Text | West, Laptop, Customer A | Names and categories |
| Whole Number | 10, 250, 1001 | Counts and integer identifiers |
| Decimal Number | 25.75, 99.95 | Measurements and fractional values |
| Fixed Decimal Number | 125.50 | Financial values requiring fixed decimal precision |
| Date | 2026-01-05 | Calendar analysis |
| Date/Time | 2026-01-05 10:30 | Timestamp analysis |
| True/False | True, False | Boolean flags |
Identifiers such as CustomerID, ProductCode, and PostalCode may look numeric but are often better treated as text. For example, a postal code such as 01234 can lose its leading zero if imported as a number.
Power Query Editor is the data preparation environment in Power BI. It lets users transform Excel and CSV data before loading it into the semantic model.
Power Query records many transformations as Applied Steps. When the query refreshes, Power Query evaluates the steps again against the source data, subject to the connector and configuration.
Example workflow:
Power Query uses the M language to define source connections and transformation steps. The following example demonstrates the general structure for reading a CSV file and assigning column types.
let
Source = Csv.Document(
File.Contents("C:\PowerBI\Sales.csv"),
[
Delimiter = ",",
Encoding = 65001,
QuoteStyle = QuoteStyle.Csv
]
),
PromotedHeaders = Table.PromoteHeaders(
Source,
[PromoteAllScalars = true]
),
ChangedTypes = Table.TransformColumnTypes(
PromotedHeaders,
{
{"OrderID", Int64.Type},
{"OrderDate", type date},
{"Product", type text},
{"Region", type text},
{"Quantity", Int64.Type},
{"SalesAmount", Currency.Type}
}
)
in
ChangedTypesThis is an illustrative example. The file path, delimiter, encoding, and column names must match the actual source. The path shown assumes that the file exists at that location on the computer running the query.
Imported data may contain errors, inconsistent labels, blank values, or unnecessary fields. Cleaning the data improves its suitability for reporting.
Remove fields that are not required for the reporting objective, provided they are not needed for relationships, calculations, or future analysis.
Example: If the report only analyzes product sales by region, an unused internal comment field may not need to be included.
Blank rows can be removed when they do not represent meaningful business records. Always inspect the source to avoid deleting legitimate records with intentionally blank fields.
Duplicate records can inflate totals and produce incorrect analysis. However, repeated values do not automatically mean that a record is duplicated. Two separate orders may have the same customer, product, and amount.
Before removing duplicates, identify the fields that define a unique record, such as a transaction ID.
Use Replace Values in Power Query to standardize known variations, such as inconsistent category names.
Example:
| Original Value | Standardized Value |
|---|---|
| North | North |
| NORTH | North |
| north | North |
Missing values should be investigated before they are removed or replaced. Depending on the business meaning, an appropriate approach may be to keep the blank, use a clearly defined unknown category, or correct the source data.
Organizations often store data in separate files for each month, region, department, or business unit. Power BI can combine files when they have compatible structures.
A company stores three files:
Each file contains OrderID, OrderDate, Product, Region, and SalesAmount. The files can be combined into one table if their schemas are compatible.
When working with files and queries, combining files commonly creates a consolidated table. In Power Query, Append queries specifically adds rows from one table to another, while Merge queries joins columns from related tables using matching fields.
| Task | Excel Workbook | CSV File |
|---|---|---|
| Connector | Excel workbook | Text/CSV |
| Typical data selection | Choose a worksheet, table, or supported named range | Inspect and parse the file as a table |
| Multiple data objects | May contain multiple worksheets and tables | Usually one table-like dataset per file |
| Delimiter selection | Usually not required for worksheet tables | Important for correctly separating fields |
| Header handling | Depends on the selected worksheet or table | Confirm whether the first row contains headers |
| Formatting | Workbook can store formatting and formulas | Plain-text values without spreadsheet formatting |
| Multiple-file workflow | Folder or SharePoint Folder connector may be useful | Folder connector can combine compatible CSV files |
Refreshing data updates the imported information using the configured source connection and query steps. Refresh behavior depends on where the source file is stored, how Power BI accesses it, and how the report is published.
When an imported Excel or CSV source changes, users can refresh the report in Power BI Desktop to retrieve the latest available data and reapply the configured query steps.
Basic workflow:
When a report is published to Power BI Service, refresh depends on the source location and connection configuration.
Cause: The file path is incorrect or the workbook has been moved.
Solution: Verify the location, filename, file extension, and access permissions. Update the source path if necessary.
Cause: A worksheet or table was renamed, removed, or redesigned after the query was created.
Solution: Open Power Query, inspect the navigation step, and update the query to reference the correct data object.
Cause: A column contains mixed values or uses a format that Power BI cannot interpret as expected.
Solution: Inspect the source values, use the appropriate type, and handle invalid records according to business rules.
Cause: The connector or environment cannot access the protected workbook in its current form.
Solution: Check the connector's current limitations and organizational security requirements. Use an approved supported file or access method.
Cause: The delimiter does not match the actual file format.
Solution: Select the correct delimiter in the Text/CSV preview or adjust the file to use a consistent separator.
Cause: The selected encoding does not match the file encoding.
Solution: Try the correct file origin or encoding option and verify that names and special characters display correctly.
Cause: The file uses a different date convention or regional format.
Solution: Use an appropriate locale when converting the column to Date or Date/Time.
Cause: Currency symbols, spaces, separators, or inconsistent values prevent correct type detection.
Solution: Clean the column, remove unwanted characters when appropriate, and assign a suitable numeric type.
Cause: The file may contain blank lines, malformed rows, inconsistent quoting, or unintended filtering steps.
Solution: Review the source and query steps, then compare row counts with the expected source totals.
Objective: Import a sales workbook, clean its data, and prepare it for reporting.
| OrderID | OrderDate | Product | Region | Quantity | SalesAmount |
|---|---|---|---|---|---|
| 1001 | 2026-01-05 | Laptop | West | 2 | 120000 |
| 1002 | 2026-01-12 | Monitor | North | 3 | 45000 |
| 1003 | 2026-02-03 | Keyboard | South | 5 | 10000 |
| 1004 | 2026-02-14 | Laptop | East | 1 | 60000 |
Open the workbook, select the sales range, and convert it into an Excel Table. Name the table SalesData.
In Power BI Desktop, select Home > Get data > Excel workbook. Browse to the workbook and select the SalesData table in Navigator.
Select Transform Data. Check the OrderDate, Quantity, and SalesAmount fields. Ensure that the date and numeric columns use appropriate data types.
Select Close & Apply. Power BI loads the prepared table into the model.
Total Sales = SUM(SalesData[SalesAmount])This DAX measure calculates the total sales amount for the current filter context.
Create a bar chart with Region as the category and Total Sales as the value. Add a card visual to display total sales across the selected data.
Check the regional totals against the source data. Confirm that no records were unintentionally excluded and that the total matches the expected sum for the imported dataset.
Objective: Import a CSV file containing sales records and ensure that its fields are interpreted correctly.
OrderID,OrderDate,Product,Region,Quantity,SalesAmount
1001,2026-01-05,Laptop,West,2,120000
1002,2026-01-12,Monitor,North,3,45000
1003,2026-02-03,Keyboard,South,5,10000
1004,2026-02-14,Laptop,East,1,60000Save the data as Sales.csv in a location accessible to Power BI Desktop.
Select Home > Get data > Text/CSV. Browse to Sales.csv and open the file.
Confirm that the data appears in six separate columns. Check that the first row is interpreted as headers and that the values are displayed correctly.
Select Transform Data. Verify the data types and correct any parsing issues. If the file uses a different delimiter or locale, adjust the relevant settings.
Apply the transformations and create a Total Sales measure or another suitable calculation. Use the fields to build a basic regional sales report.
Create an Excel workbook with sales data. Connect it to Power BI and identify the available worksheets and tables in Navigator.
Create a CSV file with at least five records. Import it using the Text/CSV connector and inspect the preview.
Import a file containing dates, quantities, prices, and product codes. Assign appropriate data types to each column.
Add a few blank rows and inconsistent category names to a practice file. Use Power Query to clean the data and document the changes.
Import a semicolon-separated file. Observe what happens when the delimiter is set incorrectly, then correct the configuration.
Create three CSV files with compatible sales columns. Use the Folder connector to combine them into one table.
Change a value in the source file, refresh the Power BI report, and confirm whether the change appears as expected.
Project Goal: Connect Power BI to Excel and CSV sales files and prepare the data for a simple sales analysis report.
The completed project should demonstrate that the learner can connect to file-based data sources, prepare the data, load it into Power BI, and use it in a basic analytical report.
Answer: An Excel workbook can contain multiple worksheets, tables, formulas, and formatting. A CSV file is a plain-text format that usually stores a single table-like dataset with fields separated by delimiters.
Answer: Select Home > Get data > Excel workbook, choose the file, select the required data object in Navigator, and choose Load or Transform Data.
Answer: Select Home > Get data > Text/CSV, choose the file, inspect the preview and delimiter, then load or transform the data.
Answer: Navigator displays available data objects from a supported source and lets users select and preview the objects they want to import.
Answer: An Excel Table defines a clear data structure, uses explicit column names, and makes it easier to select the intended data in Power BI.
Answer: Power Query connects to data and performs transformations such as changing data types, filtering rows, removing columns, and combining files before the data is loaded.
Answer: A delimiter is the character that separates fields in the file, such as a comma, semicolon, or tab.
Answer: The selected delimiter may not match the actual file format. Selecting the correct delimiter usually resolves this issue.
Answer: Load imports the selected data using the current query configuration. Transform Data opens Power Query Editor so users can clean and prepare the data before loading it.
Answer: Correct data types help Power BI perform calculations, date analysis, sorting, filtering, and aggregations correctly.
Answer: Yes. The Folder connector can combine files with compatible structures into a consolidated table, subject to appropriate query configuration.
Answer: Merge joins columns from related tables using matching fields. Append adds rows from one table to another compatible table.
Answer: The query may still reference the old file path. Update the source path and verify that the refresh environment can access the new location.
Answer: No. First identify the fields that define a unique record. Similar-looking records may represent separate legitimate transactions.
Answer: Verify the source path, credentials, access permissions, refresh requirements, gateway needs where applicable, and the accuracy of the imported data.
Connecting Excel and CSV files is an essential skill for Power BI users. Excel provides a flexible workbook structure that can contain multiple worksheets and tables, while CSV provides a simple text-based format for exchanging tabular information between applications.
To connect these sources successfully, prepare the files, choose the correct connector, inspect the available data, verify headers and delimiters, and use Power Query to correct data types and clean the information. When working with multiple files, confirm that their structures are compatible before combining them.
Finally, validate the imported data and understand the refresh requirements before publishing a report. These skills provide a strong foundation for data modeling, DAX calculations, interactive visualizations, and business reporting in Power BI.
For structured training, visit the Microsoft Power BI Training course page at JustAcademy or register for a course demo.
